package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;

import dto.QuizzesDto;

public class QuizzesDao {

	public String insertQuiz(QuizzesDto quiz) {

		if (quiz == null)
			return "Insert failed! Input data is null.";
		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "INSERT INTO quizzes "
				+ "(quiz_no, is_available, subject_subject_id) "
				+ "VALUES (?,?,?)";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setInt(1, quiz.getQuizNo());
			preparedStatement.setBoolean(2, quiz.getIsAvailable());
			preparedStatement.setString(3, quiz.getSubjectId());

			preparedStatement.execute();
			response = "Inserted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Insert failed! " + e.getMessage();
		}

		return response;
	}

	public String deleteQuiz(String quizNo) {

		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "DELETE FROM quizzes WHERE quiz_no = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, quizNo);

			preparedStatement.execute();
			response = "Deleted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Delete failed! " + e.getMessage();
		}

		return response;
	}

	public String updateQuiz(QuizzesDto quiz) {

		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "UPDATE quizzes SET is_available = ?"
				+ ", subject_subject_id = ?"
				+ " WHERE quiz_no = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);

			preparedStatement.setBoolean(1, quiz.getIsAvailable());
			preparedStatement.setString(2, quiz.getSubjectId());
			preparedStatement.setString(3, quiz.getSubjectId());

			preparedStatement.execute();
			response = "Updated successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Update failed! " + e.getMessage();
		}

		return response;
	}

	public QuizzesDto getQuizzById(String quizNo) {
		QuizzesDto quiz = null;

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM quizzes WHERE quiz_no = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, quizNo);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				quiz = new QuizzesDto();
				quiz.setQuizNo(resultSet.getInt("quiz_no"));
				quiz.setIsAvailable(resultSet.getBoolean("is_available"));
				quiz.setSubjectId(resultSet.getString("subject_subject_id"));
			}

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return quiz;
	}

	public ArrayList<QuizzesDto> getAllQuiz() {

		ArrayList<QuizzesDto> listQuiz = new ArrayList<>();

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM quizzes";

		Statement statement;
		try {
			statement = connection.createStatement();

			ResultSet resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				QuizzesDto quiz = new QuizzesDto();
				quiz.setQuizNo(resultSet.getInt("quiz_no"));
				quiz.setIsAvailable(resultSet.getBoolean("is_available"));
				quiz.setSubjectId(resultSet.getString("subject_subject_id"));
				listQuiz.add(quiz);
			}

			statement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listQuiz;
	}

}
